/**
 * Email_DBImpl.java
 */

package com.asaga.tools;

import java.beans.*;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.net.*;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;

/** 
 * Email Database Implementation
 * @author Jun Gi Ha
 */

public class Email_DBImpl extends Object implements Email{
   
    private String email;
    private DBPool connectionPool;
    private Connection connection;
    private String message;
    private String warningMessage;
    private HttpSession session;
   
    /**
     * Constructor of Email_DBImpl
     */
    public Email_DBImpl() {
        connectionPool = DBPool.getInstance();
    }
    
    /**
     * Get email
     * @return returns emails of members
     */
    public String getEmail() {
        return email;
    }

    /**
     * Set email Method
     * @param email sets member's email address
     */
    public void setEmail(String email) {
        this.email = email;
    }

    /**
     * getAllEmail Method
     * Get all user's email addresses for Email User function
     * @return returns all user's email addresses
     */
    public ResultSet getAllEmail() {
    connection = connectionPool.getConnection();
    ResultSet results = null;
    String getEmails = "SELECT email FROM person";

    try{
        java.sql.Statement stmt = connection.createStatement();
        results = stmt.executeQuery(getEmails);
        connectionPool.freeConnection(connection);
    } catch(SQLException se){
        warningMessage = "SQL Error: " + se.getMessage() + se.getSQLState();
    }        
    return results;
}

    /**
     * getUpComingEventList Method
     * Get upcoming events from DB
     * @return returns all upcoming events
     */
    public ResultSet getUpComingEventList() {
    connection = connectionPool.getConnection();
    ResultSet results = null;

    try {
     // Get present date
     java.util.Date yymmdd = new java.util.Date();
     SimpleDateFormat myformat = new SimpleDateFormat("yyyyMMdd");
     String ymd = myformat.format(yymmdd);

     // Create the SQL statement to have the list of upcoming events
     // Only have upcoming events, not past events
     String getUpcomingEvents = "select * from event where '" + ymd + "' - startDate <= 0";             
     java.sql.Statement stmt = connection.createStatement();
     results = stmt.executeQuery(getUpcomingEvents);
     connectionPool.freeConnection(connection);
     } catch(SQLException se){
        warningMessage = "SQL Error: " + se.getMessage() + se.getSQLState();
    }        
    return results;
    }

    public String evtID;
    public String ChkEvtID;
    public String evtIDs;

    /**
     * Get event ID
     * @return returns event ID
     */
    public String getEvtID() {
    return evtID;
    }

    /**
     * Set eventID Method
     * @param evtID sets one event ID that was selected by administrator
     */
    public void setEvtID(String evtID) {
    this.evtID = evtID;
    }

    /**
     * Get event IDs
     * @return returns event IDs
     */
    public String getEvtIDs() {
    return evtIDs;
    }

    /**
     * Set evtIDs Method
     * @param evtIDs sets all event IDs that were selected by administrator
     */
    public void setEvtIDs(String evtIDs) {
    this.evtIDs = evtIDs;
    }

    /**
     * getAllEmailForReminders Method
     * Get email addresses of members who signed up for the upcoming events that were selected by administrator
     * @return returns email addresses of the members
     */
    public ResultSet getAllEmailForReminders(String evtID, HttpServletRequest request, HttpSession session) {
    connection = connectionPool.getConnection();
    ResultSet results = null;
    
        // Get upcoming event IDs that were selected by administrator 
        String[] evtIDs = (String[])session.getAttribute("evtIDs");
        
        if (evtIDs != null){
        for(int i=0; i<evtIDs.length; i++) {
        evtID = evtIDs[i];

    try {
         // Get email address of person who signed up for the upcoming event
         String emails = "";
         String getFnameEmail = "select fname, email from person where personID IN (select person_personID from person_has_event where event_eventID = '" +evtID+ "')";
         java.sql.Statement stmt = connection.createStatement();
         results = stmt.executeQuery(getFnameEmail);
        
         connectionPool.freeConnection(connection);
         } catch(SQLException se){
         warningMessage = "SQL Error: " + se.getMessage() + se.getSQLState();
         }   
        }
        }
        return results; 
       }
}
